package zhtt.demo01;

import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Hibernate;
import org.hibernate.LockMode;
import org.hibernate.criterion.*;
import org.hibernate.transform.Transformers;
import org.junit.Test;
import zhtt.JunitTestUtil;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Created by zhtt on 2017/4/14.
 */
public class JunitTest01 extends JunitTestUtil{

    @Test
    /**
     * 属性等于查询
     * select * from student where age=30
     */
    public void test00(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.eq("age",30));
        List<Student> studentList=criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * 属性不等于查询
     * select * from student where age !=31
     */
    public void test01(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.ne("age",31));
        List<Student> studentList=criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * 属性模糊查询
     * select * from student where name like 'n%'
     */
    public void test02(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.like("name","n%"));
        List<Student> studentList=criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * 属性in查询
     * select * from student where age in (30,40)
     */
    public void test03(){
        Criteria criteria=session.createCriteria(Student.class);
        //方式一
        criteria.add(Restrictions.in("age",30,40));
        //方式二
        Integer[] ages={30,40};
        criteria.add(Restrictions.in("age",ages));
        //方式三
        List<Integer> ageList= Arrays.asList(ages);
        criteria.add(Restrictions.in("age",ageList));

        List<Student> studentList=criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * 返回总数
     * select count(*) from student where age in()
     */
    public void test04(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.in("age",30,40));
        Number count=(Number)criteria.setProjection(Projections.rowCount()).uniqueResult();
        System.out.println(count);
    }

    @Test
    /**
     * 返回指定字段
     */
    public void test05(){
        Criteria criteria=session.createCriteria(Student.class);
        ProjectionList projectionList= Projections.projectionList();
        projectionList.add(Projections.property("id"));
        projectionList.add(Projections.property("name"));
        criteria.setProjection(projectionList)
                .setResultTransformer(Transformers.aliasToBean(Student.class)).list();//不加这句返回的是mapList、也可以指定其它class，只要有这两个属性即可
        List<Student> studentList= ( List<Student>)criteria.list();
        System.out.println(studentList);

    }

    @Test
    /**
     * 排序
     */
    public void test06(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.addOrder(Order.asc("id"));
        List<Student> studentList= ( List<Student>)criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * 返回唯一值
     */
    public void test07(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.eq("id",1));
        Student student=(Student)criteria.uniqueResult();
        System.out.println(student);
    }

    @Test
    /**
     * 逻辑查询
     * 大于等于
     */
    public void test08(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.ge("id",1));//大于等于
        List<Student> studentList= ( List<Student>)criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * 非空、为空
     */
    public void test09(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.isNotNull("id"));//大于等于
//        criteria.add(Restrictions.isNotEmpty("roleList"));//用于集合属性
        List<Student> studentList= ( List<Student>)criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * sql语句
     */
    public void test10(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.sqlRestriction("id in (select id from zhtt11_student)"));
//        criteria.add(Restrictions.sqlRestriction("{alias}.parentId=?",  superUnitId, Hibernate.STRING));//姓名为tie的对象
//        criteria.add(Restrictions.eq("vesselUnit", vesselUnit));//也可以传入实体
        List<Student> studentList= ( List<Student>)criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * select * from student where age>1 and (name is not null or id=1)
     * or查询
     * 或查询
     */
    public void test11(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.ge("age",1));//大于等于
        criteria.add(Restrictions.or(Restrictions.isNotNull("name"),Restrictions.eq("id", 1)));
        List<Student> studentList= ( List<Student>)criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     * 时间范围、between
     */
    public void test12(){
        Criteria criteria=session.createCriteria(Student.class);
        criteria.add(Restrictions.between("age", 1, 111));
        List<Student> studentList= ( List<Student>)criteria.list();
        System.out.println(studentList);
    }

    @Test
    /**
     *
     */
    public void test13(){
        Property age = Property.forName("age");
        List<Student> studentList= session.createCriteria(Student.class)
                .add(Restrictions.disjunction()
                        .add(age.isNull())
                        .add(age.eq(new Integer(0)))
                        .add(age.eq(new Integer(1)))
                        .add(age.eq(new Integer(2)))
                        .add(age.gt(new Integer(3)))
                )
//        .add(Property.forName("name").in(new String[] {"Fritz","Izi","Pk"}))
                .list();
    }

}
